package cn.zclong.article.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jdbc.TxQueryRunner;
import cn.zclong.article.domain.Article;
import cn.zclong.article.domain.PageBean;

public class ArticleDao {
	private QueryRunner qr = new TxQueryRunner();

	/*
	 * 查询所有帖子
	 */
	public PageBean<Article> findAll(int pc, int ps) {
		try {
			
			/*
			 * 1，得到pageBean对象的pb 2,设置pb的pc当前页码 和 ps每页记录数 3，得到tr总记录数,设置给pb
			 * 4,得到beanList,设置给pb 5，返回pb
			 */
			PageBean<Article> pb = new PageBean<Article>();
			pb.setPc(pc);
			pb.setPs(ps);
			/*
			 * 得到tr
			 */
			String sql = "select count(*) from article where article.audit=?";
			Number num = (Number) qr.query(sql, new ScalarHandler(), 1);
			int tr = num.intValue();
			pb.setTr(tr);
			/*
			 * 得到beanList
			 */
			sql = "select article.*,username,t_user.uid from article left join (select uid,username from t_user) as t_user ON article.uid=t_user.uid where article.audit=? ORDER BY article.time desc limit ?,?";// 降序
			List<Article> beanList = qr.query(sql, new BeanListHandler<Article>(Article.class), 1, (pc - 1) * ps, ps);
			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/*
	 * 按id查询帖子
	 */
	public Article findAllById(String id, String readnum) {
		try {
			//先把帖子的阅读量加一
			String sql = "update article set readnum=? where id=?";
			int readnum1 = Integer.parseInt(readnum) + 1;
			qr.update(sql, readnum1, id);
			
			//查询帖子
			sql = "select article.*,username from article left join (select uid,username from t_user) as t_user ON article.uid=t_user.uid where id=?";// 降序";
			return qr.query(sql, new BeanHandler<Article>(Article.class), id);
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 返回5篇热门帖子
	 */
	public List<Article> findAllByReadnum() {
		try {
			String sql = "select article.*,username from article "
					+ "left join (select uid,username from t_user) as t_user "
					+ "ON article.uid=t_user.uid where article.audit=? ORDER BY article.readnum DESC LIMIT 5";// 降序
			return qr.query(sql, new BeanListHandler<Article>(Article.class), 1);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/*
	 * 返回所有的帖子给管理员页面
	 */
	public PageBean<Article> adminFindAll(int pc, int ps) {
		try {
			/*
			 * 1，得到pageBean对象的pb 2,设置pb的pc当前页码 和 ps每页记录数 3，得到tr总记录数,设置给pb
			 * 4,得到beanList,设置给pb 5，返回pb
			 */
			PageBean<Article> pb = new PageBean<Article>();
			pb.setPc(pc);
			pb.setPs(ps);
			/*
			 * 得到tr
			 */
			String sql = "select count(*) from article";
			Number num = (Number) qr.query(sql, new ScalarHandler());
			int tr = num.intValue();
			pb.setTr(tr);
			/*
			 * 得到beanList
			 */
			sql = "select article.*,username,t_user.uid from article left join (select uid,username from t_user) as t_user ON article.uid=t_user.uid ORDER BY article.audit asc,article.time desc limit ?,?";// 多条件排序
			List<Article> beanList = qr.query(sql, new BeanListHandler<Article>(Article.class), (pc - 1) * ps, ps);
			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Article adminFindById(String id) {
		try {
			
			//查询帖子
			String sql = "select article.*,username from article left join (select uid,username from t_user) as t_user ON article.uid=t_user.uid where id=?";
			return qr.query(sql, new BeanHandler<Article>(Article.class), id);
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 更改管理员的审核状态
	 */
	public void update(String id) {
		try {
			String sql = "update article set audit=? where id=?";
			qr.update(sql, 1, id);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void delete(String id) {		
		try {
			String sql = "delete from article where id=?";
			qr.update(sql, id);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	
}
